/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao.impl;

import dao.AgendaDAO;
import dao.AgendouDAOException;
import dao.Conexao;
import dao.HorarioDAO;
import dao.ServicosDAO;
import dao.UsuarioDAO;
import model.Agenda;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import model.Horarios;
import model.Servicos;
import model.Usuario;

/**
 *
 * @author MauroH
 */
public class AgendaDAOImpl implements AgendaDAO {

    private Connection conn;

    /**
     ***************************************************************************
     * Construtor
     * **************************************************************************
     */
    public AgendaDAOImpl() throws AgendouDAOException {
        try {
            conn = Conexao.getConnection();
        } catch (Exception e) {
            throw new AgendouDAOException("Erro: \n" + e.getMessage());
        }
    }

    @Override
    public void atualizar(Agenda agenda) throws AgendouDAOException {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public void excluir(int idhorario) throws AgendouDAOException {
        PreparedStatement ps = null;
        Connection conn1 = null;
        try {
            String sql = "DELETE FROM agendou.agenda WHERE fk_horarios = ?";
            conn1 = this.conn;
            ps = conn1.prepareStatement(sql);
            ps.setInt(1, idhorario);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new AgendouDAOException("Erro ao excluir o usuário. \n" + e);
        } finally {
            Conexao.closeConnection(conn1, ps);
        }
    }

    @Override
    public void salvar(Usuario usuario, Servicos servico, Horarios horario) throws AgendouDAOException {
        PreparedStatement ps = null;
        PreparedStatement ps1;
        PreparedStatement ps2;
        PreparedStatement ps3;
        
        ResultSet rs1;
        ResultSet rs2;
        ResultSet rs3;
        
        Connection conn1 = null;
        try {
            conn1 = this.conn;
            
            String iduser = "SELECT idusuarios FROM agendou.usuarios WHERE cpf=?";
            ps1 = conn1.prepareStatement(iduser);
            ps1.setString(1, usuario.getCpf());
            rs1 = ps1.executeQuery();
            
            String idhorario = "SELECT idhorarios FROM agendou.horarios WHERE data=? and horario=?";
            ps2 = conn1.prepareStatement(idhorario);
            ps2.setDate(1, horario.getData());
            ps2.setTime(2, horario.getHorario());
            rs2 = ps2.executeQuery();
            
            String idservico = "SELECT idservicos FROM agendou.servicos WHERE nome_servico=?";
            ps3 = conn1.prepareStatement(idservico);
            ps3.setString(1, servico.getNomeServico());
            rs3 = ps3.executeQuery();
            
            if(!rs1.next() || !rs2.next() || !rs3.next()) {
                
            } else {
                String sql = "INSERT INTO agendou.agenda (fk_usuarios, fk_horarios, fk_servico, is_ativo)"
                    + " values(?,?,?,?)";
                conn1 = conn;
                ps = conn1.prepareStatement(sql);
                ps.setInt(1, Integer.parseInt(rs1.getString("idusuarios")));
                ps.setInt(2, Integer.parseInt(rs2.getString("idhorarios")));
                ps.setInt(3, Integer.parseInt(rs3.getString("idservicos")));
                ps.setBoolean(4, true);
                ps.executeUpdate();
            }
            
        } catch (SQLException e) {
            throw new AgendouDAOException("Erro ao agendar horário. \n" + e);
        } finally {
            Conexao.closeConnection(conn1, ps);
        }
    }

    @Override
    public void salvar(Agenda agenda) throws AgendouDAOException {
        throw new UnsupportedOperationException("Not supported yet.");
    }
    
    @Override
    public Agenda getAgenda(int idhorario) throws AgendouDAOException {
        Connection conn1 = null;
        
        PreparedStatement ps = null;
        PreparedStatement ps1;
        PreparedStatement ps2;
        PreparedStatement ps3;
        
        ResultSet rs = null;
        ResultSet rs1;
        ResultSet rs2;
        ResultSet rs3;
        
        UsuarioDAO usuario = new UsuarioDAOImpl();
        HorarioDAO horario = new HorarioDAOImpl();
        ServicosDAO servico = new ServicosDAOImpl();
        
        Usuario usuarioObj = new Usuario();
        Horarios horarioObj = new Horarios();
        Servicos servicoObj = new Servicos();
        
        try {
            String sql = null;
            sql = "SELECT * FROM agendou.agenda WHERE fk_horarios = ?";
          
            conn1 = this.conn;
            ps = conn1.prepareStatement(sql);
            ps.setInt(1, (idhorario));
            rs = ps.executeQuery();
            
            
            Agenda resultado;
            if( !rs.next()) {
                resultado = new Agenda();
            } else {
                try{
                    usuarioObj = usuario.procurarUsuario(rs.getInt("fk_usuarios"));
                    horarioObj = horario.getHorario(rs.getInt("fk_horarios"));
                    servicoObj = servico.getServico(rs.getInt("fk_servico"));
                } catch (SQLException e) {}
               
                resultado = new Agenda(usuarioObj, horarioObj, servicoObj, true);
            }
            return resultado;
        } catch (SQLException e) {
            throw new AgendouDAOException("Erro ao listar os horários disponíveis " + e);
        } finally {
            Conexao.closeConnection(conn1, ps, rs);
        }
    }
    
    @Override
    public List<Horarios> getAgendas(Boolean disponivel) throws AgendouDAOException {
        PreparedStatement ps = null;
        Connection conn1 = null;
        ResultSet rs = null;
        try {
            String sql = null;
            // Se disponivel for true retorna os horários disponíveis, se não retorna os indisponíveis
            if(disponivel){
                sql = "SELECT * FROM agendou.horarios WHERE idhorarios NOT IN (SELECT fk_horarios FROM agendou.agenda WHERE is_ativo = true) ORDER BY horario ASC";
            } else {
                sql = "SELECT * FROM agendou.horarios WHERE idhorarios IN (SELECT fk_horarios FROM agendou.agenda WHERE is_ativo = true) ORDER BY horario ASC";
            }
            conn1 = this.conn;
            ps = conn1.prepareStatement(sql);
            rs = ps.executeQuery();
            List<Horarios> listHorarios = new ArrayList<Horarios>();
            while (rs.next()) {
                Horarios horarios = new Horarios(rs.getInt("idhorarios"), rs.getDate("data"), rs.getTime("horario"),
                        rs.getString("dia_semana"));
                listHorarios.add(horarios);
            }
            return listHorarios;
        } catch (SQLException e) {
            throw new AgendouDAOException("Erro ao listar os horários disponíveis " + e);
        } finally {
            Conexao.closeConnection(conn1, ps, rs);
        }
    }
    
}
